import time
import openpyxl
import pandas as pd
import random


'''
 US店铺的listing上传表格生成器
'''

'''
输出表结构
'''
out_col_names = [
    'SPU', 'ColorCode', 'SKU', 'Product ID Type', 'Product ID', 'Product Name', 'Brand', 'Selling Price', 'Shipping Weight (lbs)', 'Site Description', 'Main Image URL', 'Additional Image URL (+)',
    'Additional Image URL 1 (+)', 'Additional Image URL 2 (+)', 'Additional Image URL 3 (+)', 'Additional Image URL 4 (+)', 'Additional Image URL 5 (+)', 'California Prop 65 Warning Text',
    'Small Parts Warning Code (+)', 'Country of Origin - Textiles', 'Gender', 'Clothing Size', 'Age Group (+)', 'Color (+)', 'Color Category (+)', 'Key Features (+)', 'Key Features 1 (+)',
    'Key Features 2 (+)', 'Clothing Size Group', 'Clothing Style (+)', 'Fabric Material Name', 'Fabric Material Percentage', 'Fabric Material Name 1', 'Fabric Material Percentage 1',
    'Fabric Material Name 2', 'Fabric Material Percentage 2', 'Fabric Care Instructions (+)', 'Material (+)', 'Pattern (+)', 'Clothing Cut (+)', 'Clothing Fit', 'Clothing Weight',
    'Clothing Top Style (+)', 'Clothing Length Style', 'MSRP', 'Manufacturer Name', 'Manufacturer Part Number', 'Model Number', 'Count Per Pack', 'Total Count', 'Number of Pieces', 'Is Set',
    'Dress Shirt Size', 'Measure', 'Unit', 'Clothing Neck Style', 'Measure', 'Unit', 'Sleeve Length Style', 'Sleeve Style', 'Collar Style', 'T-Shirt Type (+)', 'Jacket Style (+)', 'Suit Breasting Style',
    'Sweater Style (+)', 'Scarf Style (+)', 'Hat Style (+)', 'Hat Size Measurement', 'Bra Style (+)', 'Bra Size', 'Measure', 'Unit', 'Bra Cup Size', 'Upper Body Strap Configuration (+)',
    'Measure', 'Unit', 'Panty Size', 'Waist Rise', 'Waist Style (+)', 'Measure', 'Unit', 'Measure', 'Unit', 'Leg Opening Cut', 'Pant Leg Cut', 'Measure', 'Unit', 'Jean Style (+)', 'Jean Wash',
    'Jean Finish (+)', 'Pant Size', 'Pant Fit (+)', 'Pant Style', 'Belt Style (+)', 'Belt Buckle Style', 'Shorts Style (+)', 'Skirt Style (+)', 'Skirt Length Style', 'Hosiery Style (+)', 'Sheerness',
    'Underpant/Swim Bottom Style', 'Underwear Style (+)', 'Underpants Type', 'Sock Size', 'Sock Style', 'Sock Rise', 'Shoe Size', 'Accent Color', 'Closure Type', 'Swimsuit Style', 'Dress Style',
    'Pajama Type', 'Outerwear Coat Jacket and Vest Type', 'GOTS Certification', 'Theme (+)', 'Occasion (+)', 'Activity (+)', 'Sport (+)', 'Season (+)', 'Weather (+)', 'Is Maternity',
    'Academic Institution', 'Autographed by', 'Recycled Material', 'Percentage of Recycled Material', 'Warranty Text', 'Warranty URL', 'Character (+)', 'Brand License (+)', 'Sports League (+)',
    'Sports Team (+)', 'Athlete (+)', 'Additional Features (+)', 'Additional Product Attribute Name', 'Additional Product Attribute Value', 'Season Year', 'Season Code', 'Variant Group ID',
    'Variant Attribute Names (+)', 'Variant Attribute Names 1 (+)', 'Is Primary Variant', 'Swatch Variant Attribute', 'Swatch Image URL', 'Fulfillment Lag Time', 'PPU Quantity of Units',
    'PPU Unit of Measure', 'Contains Electronic Component?', 'Contained Battery Type', 'Contains Chemical, Aerosol or Pesticide?', 'Multipack Quantity', 'Ships in Original Packaging', 'Site Start Date',
    'Site End Date', 'Must ship alone?', 'Additional Offer Attribute Name', 'Additional Offer Attribute Value', 'External Product ID Type', 'External Product ID', 'Product Id Update', 'SKU Update'
]

imgcol_names = [
    'Main Image URL', 
    'Additional Image URL (+)', 'Additional Image URL 1 (+)', 'Additional Image URL 2 (+)',
    'Additional Image URL 3 (+)', 'Additional Image URL 4 (+)', 'Additional Image URL 5 (+)'
]

def gen(brand, src_data):
    #print(src_data['SPUs'])
    #print(src_data['allSPUList'])

    df_out = pd.DataFrame(columns=out_col_names)
    df_out['SPU'] = src_data['allSPUList']
    df_out['ColorCode'] = src_data['allColorCodeList']
    df_out['SKU'] = src_data['allSkuList']
    df_out['Product ID Type'] = 'EAN'
    df_out['Product ID'] = ''
    for spuName in src_data['SPUs']:
        df_out.loc[df_out['SPU'] == spuName, ['Product Name']] = src_data['spuItemNameDic'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Selling Price']] = src_data['spuSellingPrice'][spuName] + round(random.uniform(-0.5, 0.5), 2)
        df_out.loc[df_out['SPU'] == spuName, ['MSRP']] = round(src_data['spuSellingPrice'][spuName]*1.2, 2)
        df_out.loc[df_out['SPU'] == spuName, ['Shipping Weight (lbs)']] = src_data['spuShippingWeight'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Site Description']] = src_data['spuSiteDescription'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Key Features (+)']] = src_data['spuKeyFeatures'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Key Features 1 (+)']] = src_data['spuKeyFeatures1'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Key Features 2 (+)']] = src_data['spuKeyFeatures2'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Clothing Size Group']] = src_data['spuClothingSizeGroup'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Clothing Style (+)']] = src_data['spuClothingStyle'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Fabric Material Name']] = src_data['spuFabricMaterialName'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Fabric Material Percentage']] = src_data['spuFabricMaterialPercentage'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Fabric Material Name 1']] = src_data['spuFabricMaterialName1'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Fabric Material Percentage 1']] = src_data['spuFabricMaterialPercentage1'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Fabric Material Name 2']] = src_data['spuFabricMaterialName2'][spuName]
        df_out.loc[df_out['SPU'] == spuName, ['Fabric Material Percentage 2']] = src_data['spuFabricMaterialPercentage2'][spuName]
        materialstr = str(src_data['spuMaterial'][spuName]).title()
        df_out.loc[df_out['SPU'] == spuName, ['Material (+)']] = '' if materialstr == 'Nan' else materialstr
        patternstr = str(src_data['spuPattern'][spuName]).title()
        df_out.loc[df_out['SPU'] == spuName, ['Pattern (+)']] = '' if patternstr == 'Nan' else patternstr
        clothingcutstr = str(src_data['spuClothingCut'][spuName]).title()
        df_out.loc[df_out['SPU'] == spuName, ['Clothing Cut (+)']] = '' if clothingcutstr == 'Nan' else clothingcutstr
        clothingfitstr = str(src_data['spuClothingFit'][spuName]).title()
        df_out.loc[df_out['SPU'] == spuName, ['Clothing Fit']] = '' if clothingfitstr == 'Nan' else clothingfitstr
        clothingtopstylestr = str(src_data['spuClothingTopStyle'][spuName]).title()
        df_out.loc[df_out['SPU'] == spuName, ['Clothing Top Style (+)']] = '' if clothingtopstylestr == 'Nan' else clothingtopstylestr
        clothinglengthstylestr = str(src_data['spuClothingLengthStyle'][spuName]).title()
        df_out.loc[df_out['SPU'] == spuName, ['Clothing Length Style']] = '' if clothinglengthstylestr == 'Nan' else clothinglengthstylestr
        clothingneckstylestr = str(src_data['spuClothingNeckStyle'][spuName]).title()
        df_out.loc[df_out['SPU'] == spuName, ['Clothing Neck Style']] = '' if clothingneckstylestr == 'Nan' else clothingneckstylestr
        sleevelengthstylestr = str(src_data['spuSleeveLengthStyle'][spuName]).title()
        df_out.loc[df_out['SPU'] == spuName, ['Sleeve Length Style']] = '' if sleevelengthstylestr == 'Nan' else sleevelengthstylestr
        sleevestylestr = str(src_data['spuSleeveStyle'][spuName]).title()
        df_out.loc[df_out['SPU'] == spuName, ['Sleeve Style']] = '' if sleevestylestr == 'Nan' else sleevestylestr
        collarstylestr = str(src_data['spuCollarStyle'][spuName]).title()
        df_out.loc[df_out['SPU'] == spuName, ['Collar Style']] = '' if collarstylestr == 'Nan' else collarstylestr
        df_out.loc[df_out['SPU'] == spuName, ['Variant Group ID']] = spuName

    df_out['Brand'] = brand
    df_out['Main Image URL'] = ''
    df_out['Additional Image URL (+)'] = ''
    df_out['Additional Image URL 1 (+)'] = ''
    df_out['Additional Image URL 2 (+)'] = ''
    df_out['Additional Image URL 3 (+)'] = ''
    df_out['Additional Image URL 4 (+)'] = ''
    df_out['California Prop 65 Warning Text'] = 'None'
    df_out['Small Parts Warning Code (+)'] = '0 - No warning applicable'
    df_out['Country of Origin - Textiles'] = 'Imported'
    df_out['Gender'] = 'Female'
    df_out['Clothing Size'] = src_data['allSizeList']
    df_out['Age Group (+)'] = 'Adult'
    df_out['Color (+)'] = src_data['allColorList']
    df_out['Color Category (+)'] = src_data['allColorCateList']
    df_out['Fabric Care Instructions (+)'] = 'Machine washable'
    df_out['Clothing Weight'] = 'Lightweight'

    df_out['Manufacturer Name'] = brand
    df_out['Manufacturer Part Number'] = src_data['allSkuList']
    df_out['Model Number'] = ''
    df_out['Count Per Pack'] = 1
    df_out['Total Count'] = 1
    df_out['Number of Pieces'] = 1
    df_out['Is Set'] = 'No'
    df_out['Dress Shirt Size'] = ''
    df_out['Measure'] = ''
    df_out['Unit'] = ''
    df_out['Measure'] = ''
    df_out['Unit'] = ''
    df_out['T-Shirt Type (+)'] = ''
    df_out['Jacket Style (+)'] = ''
    df_out['Suit Breasting Style'] = ''
    df_out['Sweater Style (+)'] = ''
    df_out['Scarf Style (+)'] = ''
    df_out['Hat Style (+)'] = ''
    df_out['Hat Size Measurement'] = ''
    df_out['Bra Style (+)'] = ''
    df_out['Bra Size'] = ''
    df_out['Measure'] = ''
    df_out['Unit'] = ''
    df_out['Bra Cup Size'] = ''
    df_out['Upper Body Strap Configuration (+)'] = ''
    df_out['Measure'] = ''
    df_out['Unit'] = ''
    df_out['Panty Size'] = ''
    df_out['Waist Rise'] = ''
    df_out['Waist Style (+)'] = ''
    df_out['Measure'] = ''
    df_out['Unit'] = ''
    df_out['Measure'] = ''
    df_out['Unit'] = ''
    df_out['Leg Opening Cut'] = ''
    df_out['Pant Leg Cut'] = ''
    df_out['Measure'] = ''
    df_out['Unit'] = ''
    df_out['Jean Style (+)'] = ''
    df_out['Jean Wash'] = ''
    df_out['Jean Finish (+)'] = ''
    df_out['Pant Size'] = ''
    df_out['Pant Fit (+)'] = ''
    df_out['Pant Style'] = ''
    df_out['Belt Style (+)'] = ''
    df_out['Belt Buckle Style'] = ''
    df_out['Shorts Style (+)'] = ''
    df_out['Skirt Style (+)'] = ''
    df_out['Skirt Length Style'] = ''
    df_out['Hosiery Style (+)'] = ''
    df_out['Sheerness'] = ''
    df_out['Underpant/Swim Bottom Style'] = ''
    df_out['Underwear Style (+)'] = ''
    df_out['Underpants Type'] = ''
    df_out['Sock Size'] = ''
    df_out['Sock Style'] = ''
    df_out['Sock Rise'] = ''
    df_out['Shoe Size'] = ''
    df_out['Accent Color'] = ''
    df_out['Closure Type'] = ''
    df_out['Swimsuit Style'] = ''
    df_out['Dress Style'] = ''
    df_out['Pajama Type'] = ''
    df_out['Outerwear Coat Jacket and Vest Type'] = ''
    df_out['GOTS Certification'] = ''
    df_out['Theme (+)'] = 'Fashion'
    df_out['Occasion (+)'] = 'Halloween; Christmas; Wedding; Anniversary; Work; School; Weekend; Vacation'
    df_out['Activity (+)'] = ''
    df_out['Sport (+)'] = ''
    df_out['Season (+)'] = ''
    df_out['Weather (+)'] = 'All-Weather'
    df_out['Is Maternity'] = 'No'
    df_out['Academic Institution'] = ''
    df_out['Autographed by'] = ''
    df_out['Recycled Material'] = ''
    df_out['Percentage of Recycled Material'] = ''
    df_out['Warranty Text'] = ''
    df_out['Warranty URL'] = ''
    df_out['Character (+)'] = ''
    df_out['Brand License (+)'] = ''
    df_out['Sports League (+)'] = ''
    df_out['Sports Team (+)'] = ''
    df_out['Athlete (+)'] = ''
    df_out['Additional Features (+)'] = ''
    df_out['Additional Product Attribute Name'] = ''
    df_out['Additional Product Attribute Value'] = ''
    thisYear = time.strftime('%Y', time.localtime(time.time()))
    df_out['Season Year'] = thisYear
    df_out['Season Code'] = '1- Spring'
    df_out['Variant Attribute Names (+)'] = 'color'
    df_out['Variant Attribute Names 1 (+)'] = 'clothingSize'
    df_out['Is Primary Variant'] = src_data['allIsPrimary']
    df_out['Swatch Variant Attribute'] = 'color'
    df_out['Swatch Image URL'] = ''
    df_out['Fulfillment Lag Time'] = 7
    df_out['PPU Quantity of Units'] = ''
    df_out['PPU Unit of Measure'] = ''
    df_out['Contains Electronic Component?'] = 'No'
    df_out['Contained Battery Type'] = ''
    df_out['Contains Chemical, Aerosol or Pesticide?'] = 'No'
    df_out['Multipack Quantity'] = ''
    df_out['Ships in Original Packaging'] = 'Yes'
    todayStr = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    endayStr = time.strftime('%Y-%m-%d', time.localtime(time.time()+3122064000))
    df_out['Site Start Date'] = todayStr
    df_out['Site End Date'] = endayStr
    df_out['Must ship alone?'] = 'No'
    df_out['Additional Offer Attribute Name'] = ''
    df_out['Additional Offer Attribute Value'] = ''
    df_out['External Product ID Type'] = ''
    df_out['External Product ID'] = ''
    df_out['Product Id Update'] = 'No'
    df_out['SKU Update'] = 'No'

    # 主图写入表结构
    for spucolor,imgList in src_data['skuImgList'].items():
        spu,color = spucolor.split('_')
        #print(spu, color)
        count_url = len(imgList)
        count_imgcol = len(imgcol_names)
        top = count_url if count_url<=count_imgcol else count_imgcol
        for i in range(top):
            #print(spu,color,imgList[i])
            df_out.loc[(df_out['SPU'] == spu) & (df_out['ColorCode'] == color), imgcol_names[i]] = imgList[i][1] # 存储的是tuple
    # swatch image 写入表结构
    for spucolor,url in src_data['skuSwatchImgList'].items():
        spu,color = spucolor.split('_')
        #print(spu,color,url)
        df_out.loc[(df_out['SPU'] == spu) & (df_out['ColorCode'] == color), 'Swatch Image URL'] = url

    return df_out